package com.personal.utils.excel.utils;

import com.personal.utils.excel.constant.ConstantParams;
import com.personal.utils.excel.utils.bean.CellFormatBean;
import com.personal.utils.excel.utils.bean.FileDataBean;
import com.personal.utils.excel.utils.bean.SheetDataBean;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.List;
import java.util.Map;

/**
 * description: WriteExcelFileUtil <br>
 * date: 2020/4/18 23:18 <br>
 * author: wenjun.dai <br>
 * version: 1.0 <br>
 */
@Slf4j
public class WriteExcelFileUtil {

    /**
     * 在默认目录生成文件
     *
     * @param fileDataBean
     * @param fileName
     * @return
     */
    public static File writeFileDefault(FileDataBean fileDataBean, String fileName) {
        String filePath = ConstantParams.TMP_DIR_IMPORT + "/" + fileName;
        return writeFile(fileDataBean, filePath);
    }

    public static File writeFileDefaultBySheet(SheetDataBean sheetDataBean, String fileName) {
        String filePath = ConstantParams.TMP_DIR_IMPORT + "/" + fileName;
        return writeFileBySheet(sheetDataBean, filePath);
    }

    /**
     * 在指定位置生成文件
     *
     * @param fileDataBean
     * @param filePath
     * @return
     */
    public static File writeFile(FileDataBean fileDataBean, String filePath) {
        File file = new File(filePath);
        return writeFile(fileDataBean, file);
    }

    /**
     * 在指定位置生成文件
     *
     * @param sheetDataBean
     * @param filePath
     * @return
     */
    public static File writeFileBySheet(SheetDataBean sheetDataBean, String filePath) {
        File file = new File(filePath);
        return writeFileBySheet(sheetDataBean, file);
    }

    /**
     * 生成文件
     *
     * @param fileDataBean
     * @param file
     * @return
     */
    public static File writeFile(FileDataBean fileDataBean, File file) {

        List<String> sheetNameList = fileDataBean.getSheetNameList();
        List<SheetDataBean> sheetDataBeanList = fileDataBean.getSheetDataBeanList();
        WritableWorkbook workbook = null;

        // 创建写工作簿对象
        try {
            workbook = jxl.Workbook.createWorkbook(file);
            for (int i = 0; i < sheetNameList.size(); i++) {
                String sheetName = sheetNameList.get(i);
                WritableSheet sheet = workbook.createSheet(sheetName, i);
                SheetDataBean sheetDataBean = sheetDataBeanList.get(i);
                writeSheet(sheet, sheetDataBean);
            }
            //开始执行写入操作
            workbook.write();


        } catch (Exception e) {
            log.error("生成数据文件异常!", e);
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error("生成数据文件异常!", e);
                }
            }
        }
        return file;
    }

    /**
     * 只有一个 sheet 的文件
     *
     * @param sheetDataBean
     * @param file
     * @return
     */
    public static File writeFileBySheet(SheetDataBean sheetDataBean, File file) {

        WritableWorkbook workbook = null;
        // 创建写工作簿对象
        try {
            workbook = jxl.Workbook.createWorkbook(file);
            String sheetName = sheetDataBean.getSheetName();
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            writeSheet(sheet, sheetDataBean);
            //开始执行写入操作
            workbook.write();
        } catch (Exception e) {
            log.error("生成数据文件异常!", e);
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error("生成数据文件异常!", e);
                }
            }
        }
        return file;
    }

    /**
     * 写 sheet 页数据
     *
     * @param sheet
     * @param sheetDataBean
     * @return
     * @throws WriteException
     */
    public static WritableSheet writeSheet(WritableSheet sheet, SheetDataBean sheetDataBean) throws WriteException {
        List<String> firstColumnList = sheetDataBean.getFirstColumnList();
        List<String> insteadColumnList = sheetDataBean.getInsteadColumnList();
        List<CellFormatBean> cellFormatBeanList = sheetDataBean.getCellFormatBeanList();

        //第一行设置
        // 设置字体;
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false,
                UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat cellFormat = new WritableCellFormat(font);
//            // 设置背景颜色;
//            cellFormat.setBackground(Colour.WHITE);
//            // 设置文字居中对齐方式;
//            cellFormat.setAlignment(Alignment.CENTRE);
        // 设置自动换行;
//        cellFormat.setWrap(true);
        for (int i = 0; i < firstColumnList.size(); i++) {
            String columnName = firstColumnList.get(i);
            Label label = getLabel(cellFormatBeanList, columnName, columnName, i, 0, cellFormat);
            sheet.addCell(label);
            sheet.setColumnView(i, 12);
        }
        //第二行设置
        // 设置字体;
        WritableFont dataFont = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false,
                UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat cellDataFont = new WritableCellFormat(dataFont);
        // 设置背景颜色;
//            cellDataFont.setBackground(Colour.WHITE);
//            // 设置文字居中对齐方式;
//            cellDataFont.setAlignment(Alignment.CENTRE);
        // 设置自动换行;
//        cellDataFont.setWrap(true);

        List<Map<String, String>> sheetDataList = sheetDataBean.getSheetDataList();
        // 记录行数
        int n = 1;
        //数据写入
        for (Map<String, String> map : sheetDataList) {
            for (int i = 0; i < firstColumnList.size(); i++) {
                Label label;
                if (null != insteadColumnList && !insteadColumnList.isEmpty()) {
                    // 使用别名作为 key
                    String key = insteadColumnList.get(i);
                    String value = map.get(key);
                    label = getLabel(cellFormatBeanList, key, value, i, n, cellDataFont);
                } else {
                    // 使用列名作为 key
                    String key = firstColumnList.get(i);
                    String value = map.get(key);
                    label = getLabel(cellFormatBeanList, key, value, i, n, cellDataFont);
                }
                sheet.addCell(label);
            }
            n++;
        }

        return sheet;
    }

    /**
     * 组装自定义格式的 label
     *
     * @param cellFormatBeanList
     * @param keyStr
     * @param valueStr
     * @param i
     * @param n
     * @param defaultCellFormat
     * @return
     */
    private static Label getLabel(List<CellFormatBean> cellFormatBeanList, String keyStr, String valueStr,
                                  Integer i, Integer n, WritableCellFormat defaultCellFormat) throws WriteException {
        Label label;
        CellFormatBean cellFormatBean = checkSelfFormat(cellFormatBeanList, keyStr, valueStr);
        if (null == cellFormatBean) {
            label = new Label(i, n, valueStr, defaultCellFormat);
        } else {
            WritableCellFormat selfCellFormat = cellFormatBean.getCellFormat();
            label = new Label(i, n, valueStr, selfCellFormat);
        }
        return label;
    }


    /**
     * 获取特殊格式
     *
     * @param cellFormatBeanList
     * @param keyStr
     * @param valueStr
     * @return
     */
    private static CellFormatBean checkSelfFormat(List<CellFormatBean> cellFormatBeanList,
                                                  String keyStr, String valueStr) {
        if (null == cellFormatBeanList || cellFormatBeanList.isEmpty()) {
            return null;
        }
        // 遍历
        CellFormatBean cellFormatBean = null;
        for (CellFormatBean bean : cellFormatBeanList) {
            String key = bean.getKey();
            String value = bean.getValue();
            if (key.equals(keyStr) && value.equals(valueStr)) {
                cellFormatBean = bean;
            }
        }
        return cellFormatBean;
    }




}
